import pymysql
import pandas as pd
import pymysql.cursors

class MysqulUtils(object):
    
    def __init__(self):
        self.conn=pymysql.connect(
            host='127.0.0.1',
            user='root',
            passwd='root',
            db='tushare',
            port='3306',
            charset='utf-8'
            
        )
        
class classIfication(object):
    
    def __init__(self):
        pass
    
    def get_fina_indicator(self,conn):
        
        
        cursor=conn.cursor(cursor=pymysql.cursor=pymysql.cursors.DictCursor)
        sql=""""
        SELECT ts_code, ann_date, eps, total_revenue_ps, undist_ps, gross_margin, fcff,fcfe, tangiible_asset, bps, grossprofit_margin,npta FROM financial_data WHERE ann_date < '2024-01-01' AND ann_date
        """
        
        cursor.execute(sql)
        
        ret= cursor.fetchall()
        
        df = pd.DataFrame(ret)
        
        df1= df.dropna(sybset=['eps','total_revenue_ps','undist_profit_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin','npta'])
        
        df1=df1.reset_index(drop=False)
        #print(df1.head)
        return df1
    
    def get_daily(self,conn,df):
        cursor =conn.cursor(cursor=pymysql.cursors.DictCursor)
        new_list = []
        
        for i in range(len(df['ts_code'])):
            print(i)
            ann_date_str =df['ann_date'].strftime('%Y%m%d')
            sql = "select trade_date, closes from date_1 where ts_code=\'"+ df['ts_code'][i] +"' and trade_date > Date('"+ann_date_str +"')"
            cursor.execute(sql)
            
            ret=cursor.fetchall()
            df1=pd.DataFrame(ret)
           
try:
               if len(df1) > 0:
                   max_close =df1['closes'].max()
                   min_close=df1['closes'].min()
                   the_slose =df1['closes'].iloc[1]
                   new_list.append({
                       'ts_code':df['ts_code'][i],
                       'ann_date':df['ts_code'],
                       'max_close':max_close,
                       'min_close':min_close,
                       'the_slose':the_slose,
                       
                       'eps':df['eps'][i],
                       'total_revenue_ps':df['total_revenue_ps'][i],
                       'undist_profit_ps':df['undist_profit_ps'][i],
                       'fcff':df['fcff'][i],
                       'fcfe':df['fcfe'][i],
                       'tangible_asset':df['tangible_asset'][i],
                       'bps':df['bps'][i],
                       'grossprofit_margin':df['rossprofit_margin'][i],
                       'npta':df['npta'][i],
                       
                   })
except Exception as e:
          print(e)
          
df2=pd.DataFrame(new_list)
df2.to_csv('daily.csv',index=False)  
pass


if __name__ == '__main__':
    mu=MysqlUtils()
    ci = classIfication()
    ci.get_fina_indicator(mu.conn)
